<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Sn
 *
 * @author Massri
 */
class Application_Model_Sn {

    //put your code here
    private $_sn;
    private $_db;

    function __construct() {
        $registry = Zend_Registry::getInstance();
        $this->_db = $registry->database;
    }

    public function getRandom() {
        do {
            $agt_no = mt_rand(1000000, 9999999);
            $valid = true;
            if (preg_match('/(\d)\1\1/', $agt_no))
                $valid = false; // Same digit three times consecutively
            elseif (preg_match('/(\d).*?\1.*?\1.*?\1/', $agt_no))
                $valid = false; // Same digit four times in string
        } while ($valid === false);
        return $agt_no;
    }
    
    public function generatePIN() {
        do {
          $pin_no = mt_rand(100000,999999);
          $valid = true;
          if (preg_match('/(\d)\1\1/', $pin_no))
            $valid = false; // Same digit three times consecutively
          elseif (preg_match('/(\d).*?\1.*?\1.*?\1/', $pin_no))
            $valid = false; // Same digit four times in string
        } while ($valid === false);
        return $pin_no;
    }
    
    public function genSN(){    
        
        $gen = $this->getRandom();
        if ($gen == ""){
                $this->genSN();
        }else{
            //$sn = $gen.$sum;
            $sn = 'NIK'.$gen;
            //
            // comment for testing, DB not exist
            if(!$this->isExistSn($sn)){
               $pin = $this->generatePIN();
               
               $this->_db->insert('sn',array('sn_code'=>$sn, 'sn_pin' => $pin,'sn_status'=> -2));
               return $sn.','.$pin;
               //echo ($i+1).". XF".$sn->genSN() ."<br>";
            }else{
               $this->genSN();
            }
            
        }
    }
    
    public function isExistSn($sn){
        $sql = "select count(*) cnt from sn where sn_code = '$sn'";
        
        $r = $this->_db->fetchRow($sql);
        if($r['cnt'] > 0 ){
            return true;
        }else{
            return false;
        }
        
    }

    private function isExistSn2($sn, $pin){

        $sql = "select count(*) num from sn where sn_code = '$sn' and sn_pin = '$pin' and sn_status in (-1, 0)";
        
        $r = $this->_db->fetchRow($sql);
        if($r['num']>0){
            return true;
        }else{
            //$this->_db->insert('SN',array('serial_number'=>$sn,'status'=>0));
            return false;
        }
    }

    private function isUsedSn($sn, $memberId = ""){
        $sql = "select count(*) num from members where member_code = '$sn'";

        if ($memberId != ""){
            $sql .= " and member_id <> $memberId";
        }

        $r = $this->_db->fetchRow($sql);

        if($r['num']>0){
            return true;
        }else{
            //$this->_db->insert('SN',array('serial_number'=>$sn,'status'=>0));
            return false;
        }
    }


    public function isValidSn($sn, $pin, $memberId = ""){

        $prefix = substr($sn, 0, 3);
        //$num   = trim(substr($sn, 3));

        if ($prefix == "NIK"){

            //Ceck is valid SN
            //if ($this->luhnChecksum($num) == $sum){
                //Ceck is exist SN
                if ($this->isExistSn2($sn, $pin)){

                    //Ceck is used SN
                    if ($this->isUsedSn($sn, $memberId)){

                        return false;
                    }else{
                        return true;
                    }
                }else{
                    return false;
                }
            /*
            }else{
                return false;
            }
            */
        }else{
            return false;
        }

    }

    
    public function getSN($num, $sn, $stts, $start, $limit){

        if ($num != ""){
            if ($limit != ""){

                $sql = "select count(1) as total
                        from sn where sn_status = -2";

                $result = $this->_db->fetchRow($sql);
                $rows = $result['total'];

                $data = array($rows);


            }/*else{

                $sql = "select concat('XT',serial_number) as serial_number, pin, if(status < 0, 'Tidak Aktif', 'Aktif') as status
                    from SN
                    where status = -2 limit 0, $num";
            }
             *
             */

            $sql = "select sn_code as serial_number, sn_pin as pin, if(sn_status < 0, 'Tidak Aktif', 'Aktif') as status
                    from sn
                    where sn_status = -2 ";



            if ($limit != ""){
                $sql .= " limit $start, $limit";
            }



        }else{

            if ($limit != ""){

                $sql = "select count(1) as total
                        from sn where /*status = -1
                        and*/ sn_code like '%$sn%'";

                if ($stts != ""){
                     $sql .= " and sn_status = $stts";
                }else{
                    $sql .= " and sn_status > -2 ";
                }

                $result = $this->_db->fetchRow($sql);
                $rows = $result['total'];

                $data = array($rows);
            }

            $sql = "select sn_code as serial_number, sn_pin as pin, if(sn_status < 0, 'Pasif/Tidak Aktif', if(sn_status = 0, 'Aktif', 'Sudah Digunakan')) as status
                    from sn
                    where /*status = -1 and*/ sn_code like '%$sn%' ";

            if ($stts != ""){
                 $sql .= " and sn_status = $stts";
            }else{
                $sql .= " and sn_status > -2 ";
            }

            if ($limit != ""){
                $sql .= " limit $start, $limit";
            }
        }


        $result = $this->_db->fetchAll($sql);

        if ($limit != ""){
            array_push($data, $result);
        }else{
            $data = $result;
        }

        //print_r($data);

        return $data;
    }

    function updateStatusSN($sn, $stts){
        $this->_db->beginTransaction();

        $result = array();

        try {

            /*
             * Update SN
             */
            $sql = "update sn set sn_status = $stts where sn_code in ($sn) and sn_status = -2";

            $res = $this->_db->query($sql);

            if ($res){

                $this->_db->commit();

                $result = array(
                    'code'  => 0,
                    'msg'   => 'Update Sukses',
                    'sn'  => $sn
                );

                return $result;

            }


        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'sn'  => $sn
            );

            return $result;
        }
    }


    function aktivasiSN($snList, $flag, $sn) {

        $this->_db->beginTransaction();

        $ret = array();

        $msg = "";

        try {

            if ($flag == 1){ //Aktifkan yang dipilih
                $msg = "Diaktifkan";
                $sql = "update sn set sn_status = 0 where sn_code in ($snList) and sn_status = -1";
            }else if ($flag == 2){ //Aktifkan semua
                $msg = "Diaktifkan";
                $sql = "update sn set sn_status = 0 where sn_code like '%$sn%' and sn_status = -1";
            }else if ($flag == 3){  //Pasifkan semua
                $msg = "Dipasifkan";
                $sql = "update sn set sn_status = -1 where sn_code like '%$sn%' and sn_status = 0";
            }


            $res = $this->_db->query($sql);

            $this->_db->commit();



            $ret = array(
                    'code'  => 0,
                    'msg'   => 'SN Berhasil '.$msg
                );



        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => 'Aktifasi SN Gagal: '.$errMsg
            );
        }

        return $ret;
    }
}
